import pandas as pd
import openpyxl
import win32com.client as win32
import os

current_path = os.getcwd()

if __name__ == '__main__':                                  #要要求他们把输入的数据改成xlsx
	original_path = (current_path + "\\发票号模板.xlsx")
	df = pd.read_excel(original_path,engine='openpyxl',sheet_name='原账单')    #转换格式后输出的文件，读入进行数据处理   
	df_dropped = df.drop(df.index[:2])  
	df_dropped.columns = ['状态', '货单编号','图片','厂编','钻石尺寸','手寸','货品名称','件数','件重（g）','净重（g）','含耗重','金价','重量（主石）','粒数（主石）','单价（主石）','金额（主石）','主镶','重量','粒数','单价','金额','红宝重量','红宝价格','露珠或分色费','镶工费','起版费','工费','配件费','证书','应付款']  
	df_dropped.loc[:, '含耗重'] = df_dropped['净重（g）'] * 1.15
	df_dropped.loc[:, '主镶'] = df_dropped['主镶'] * 2
	df_dropped.loc[:, '露珠或分色费'] = df_dropped['露珠或分色费'] * 2
	df_dropped.loc[:, '镶工费'] = df_dropped['粒数'] * 5
	df_dropped.loc[:, '起版费'] = df_dropped['起版费'] + 70
	df_dropped.loc[:, '工费'] = df_dropped['工费'].where(df_dropped['工费'] >= 120, 120) + df_dropped['工费'].gt(120) * 70
	df_dropped.loc[:, '配件费'] = df_dropped['配件费'].apply(lambda x: x * 1.5 if x < 100 else x + 1.3)  
	output = df_dropped.reset_index(drop=True)
	output.to_excel('发票.xlsx', index=False)